/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2016
 */

package com.jzwl.xydk.manager.user.userskillinfo.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;



@Repository("userSkillinfoDao")
public class UserSkillinfoDao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	
	public String addUserSkillinfo(Map<String, Object> map) {
		
		Date date = new Date();
		
		String id = Sequence.nextId();
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("id",id);
		map.put("viewNum",  0);
		map.put("createDate",  date);
		map.put("isDelete",  0);
		map.put("isDisplay",  0);
		
		String sql = "insert into `xiaoka-xydk`.user_skillinfo " 
				 + " (id,basicId,skillFatId,skillSonId,skillName,textURL,videoURL,skillPrice,serviceType,skillDepict,viewNum,createDate,isDelete,isDisplay) " 
				 + " values "
				 + " (:id,:basicId,:skillFatId,:skillSonId,:skillName,:textURL,:videoUrl,:skillPrice,:serviceType,:skillDepict,:viewNum,:createDate,:isDelete,:isDisplay)";
		
		baseDAO.executeNamedCommand(sql, map);
		
		return id;
	}
	
	
	public boolean addImage(String answer,String basicId) {
		
		Date date = new Date();
		
		Map<String,Object> map = new HashMap<String, Object>();
		
		String id = Sequence.nextId();
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("id",id);
		map.put("imgType",  1);
		map.put("createDate",  date);
		map.put("imgURL",  answer);
		map.put("skillId",  basicId);
		
		String sql = "insert into `xiaoka-xydk`.user_skill_image " 
				 + " (id,skillId,imgURL,imgType,createDate) " 
				 + " values "
				 + " (:id,:skillId,:imgURL,:imgType,:createDate)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public String getColumns() {
		return ""
				+" t.id as id,"
				+" t.basicId as basicId,"
				+" t.skillFatId as skillFatId,"
				+" t.skillSonId as skillSonId,"
				+" t.skillName as skillName,"
				+" t.textURL as textUrl,"
				+" t.otherOpus as otherOpus,"
				+" t.videoURL as videoUrl,"
				+" t.skillPrice as skillPrice,"
				+" t.serviceType as serviceType,"
				+" t.skillDepict as skillDepict,"
				+" t.viewNum as viewNum,"
				+" t.createDate as createDate,"
				+" t.isDelete as isDelete"
				;
	}
	

	public PageObject queryUserSkillinfoList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql="select " + getColumns() + " from `xiaoka-xydk`.user_skillinfo t where t.isDelete = 0 ";
		
		  	if(null !=map.get("basicId") && StringUtils.isNotEmpty(map.get("basicId").toString())){
			  		sql=sql+ " and t.basicId  = " + map.get("basicId") +"";
		  	}
		  	if(null !=map.get("skillFatId") && StringUtils.isNotEmpty(map.get("skillFatId").toString())){
			  		sql=sql+ " and t.skillFatId  = " + map.get("skillFatId") +"";
		  	}
		  	if(null !=map.get("skillSonId") && StringUtils.isNotEmpty(map.get("skillSonId").toString())){
			  		sql=sql+ " and t.skillSonId  = " + map.get("skillSonId") +"";
		  	}
		  	if(null !=map.get("skillName") && StringUtils.isNotEmpty(map.get("skillName").toString())){
			  		sql=sql+ " and t.skillName  = " + map.get("skillName") +"";
		  	}
		  	if(null !=map.get("textUrl") && StringUtils.isNotEmpty(map.get("textUrl").toString())){
			  		sql=sql+ " and t.textURL  = " + map.get("textUrl") +"";
		  	}
		  	if(null !=map.get("otherOpus") && StringUtils.isNotEmpty(map.get("otherOpus").toString())){
			  		sql=sql+ " and t.otherOpus  = " + map.get("otherOpus") +"";
		  	}
		  	if(null !=map.get("videoUrl") && StringUtils.isNotEmpty(map.get("videoUrl").toString())){
			  		sql=sql+ " and t.videoURL  = " + map.get("videoUrl") +"";
		  	}
		  	if(null !=map.get("skillPrice") && StringUtils.isNotEmpty(map.get("skillPrice").toString())){
			  		sql=sql+ " and t.skillPrice  = " + map.get("skillPrice") +"";
		  	}
		  	if(null !=map.get("serviceType") && StringUtils.isNotEmpty(map.get("serviceType").toString())){
			  		sql=sql+ " and t.serviceType  = " + map.get("serviceType") +"";
		  	}
		  	if(null !=map.get("skillDepict") && StringUtils.isNotEmpty(map.get("skillDepict").toString())){
			  		sql=sql+ " and t.skillDepict  = " + map.get("skillDepict") +"";
		  	}
		  	if(null !=map.get("viewNum") && StringUtils.isNotEmpty(map.get("viewNum").toString())){
			  		sql=sql+ " and t.viewNum  = " + map.get("viewNum") +"";
		  	}
		  	if(null !=map.get("createDate") && StringUtils.isNotEmpty(map.get("createDate").toString())){
			  		sql=sql+ " and t.createDate >= " + map.get("createDateBegin") +"";
			  		sql=sql+ " and t.createDate <= " + map.get("createDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("isDelete") && StringUtils.isNotEmpty(map.get("isDelete").toString())){
			  		sql=sql+ " and t.isDelete  = " + map.get("isDelete") +"";
		  	}
					
					
		sql=sql+ " order by id ";
		
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		
		return po;
	}

	public boolean updateUserSkillinfo(Map<String, Object> map) {
		
		
		//id=1477652416195005, skillFatId=1476432959932003, skillName=bbbbb, skillSonId=1476435764295001, basicId=1477640328531001, videoUrl=bbbbb, thumbnailUrl=, skillDepict=bbbbbbbbbbbbbbb, skillPrice=2.0, serviceType=1, textURL=http://www.baidu.com
		
		
		
		String sql ="update `xiaoka-xydk`.user_skillinfo set "
				+ " skillFatId=:skillFatId,skillSonId=:skillSonId,skillName=:skillName,textURL=:textURL,videoUrl=:videoUrl,skillPrice=:skillPrice,serviceType=:serviceType,skillDepict=:skillDepict "
				+ " where id=:id";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteUserSkillinfo(Map<String, Object> map) {
		
		String sql ="update `xiaoka-xydk`.user_skillinfo set "
				+ " isDelete=1 "
				+ " where id=:id";

		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public String getColumns2() {
		return ""
				+" t.id as id,"
				+" t.basicId as basicId,"
				+" t.skillFatId as skillFatId,"
				+" t.skillSonId as skillSonId,"
				+" t.skillName as skillName,"
				+" t.textURL as textUrl,"
				+" t.otherOpus as otherOpus,"
				+" t.videoURL as videoUrl,"
				+" t.skillPrice as skillPrice,"
				+" t.serviceType as serviceType,"
				+" t.skillDepict as skillDepict,"
				+" t.viewNum as viewNum,"
				+" t.createDate as createDate,"
				+" t.isDelete as isDelete,"
				+" usf.id as fid,"
				+" usf.className as f_className,"
				+" usf.imageURL as imageURL,"
				+" uss.id as sid,"
				+" uss.className as s_className"
				;
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getById(Map<String, Object> map) {
		
		Map<String,Object> resMap = new HashMap<String,Object>();

		String sql = "select " + getColumns2() + " from `xiaoka-xydk`.user_skillinfo t"
				+ " left join `xiaoka-xydk`.user_skillclass_father usf on t.skillFatId= usf.id"
				+ " left join `xiaoka-xydk`.user_skillclass_son uss on t.skillSonId=uss.id"
				+ " where t.id = "+ map.get("id") + "";
		
		resMap = baseDAO.queryForMap(sql);
		
		return resMap;
	
	}
	
	public List<Map<String, Object>> getClassF() {
		String sql = "select t.* from `xiaoka-xydk`.user_skillclass_father t where t.isDelete = 0";
		return baseDAO.queryForList(sql);
	}
	
	public List<Map<String, Object>> getClassS() {
		String sql = "select t.* from `xiaoka-xydk`.user_skillclass_son t where t.isDelete = 0";
		return baseDAO.queryForList(sql);
	}
	
	public List<Map<String, Object>> getImages(String id) {
		String sql = "select t.* from `xiaoka-xydk`.user_skill_image t where t.skillId="+id;
		return baseDAO.queryForList(sql);
	}
	
	
	public List getClassSBySid(Map<String, Object> map) {
		String sql = "select t.* from `xiaoka-xydk`.user_skillclass_son t where t.fatherId='"+map.get("sid")+"'";
		return baseDAO.queryForList(sql);
	}
	/**
	 * 删除图片 
	 * @param map
	 * @return
	 */
	public boolean deleteImage(Map<String, Object> map) {
		
		String sql = "delete from `xiaoka-xydk`.user_skill_image where skillId in ("+ map.get("id")+ " ) ";

		return baseDAO.executeNamedCommand(sql, map);
	}

}
